-- @owner: lihongji
-- @date: 2022/07/21
-- @testpoint: 隐含转换

--step1:建表;expect:成功
drop table if exists t_ustore_nullif_0003;
create table t_ustore_nullif_0003(
col_1 int,
col_2 decimal,
col_3 smallint,
col_4 char(30),
col_5 real,
col_6 numeric,
col_7 int,
col_8 timestamp,
col_9 clob,
col_10 number(6,2),
col_11 decimal(6,2),
col_12 varchar2(50),
col_13 varchar(30),
col_14 numeric(12,6),
col_15 date,
constraint pk_nullif_id primary key(col_1)
) with (storage_type=ustore);

--step2:建序列;expect:成功
drop sequence if exists sq_ustore_nullif_0003;
create sequence sq_ustore_nullif_0003 increment by 1 start with 100000;

--step3:插入数据;expect:成功
begin
 for i in 1..1000 loop
      insert into t_ustore_nullif_0003 values(
      sq_ustore_nullif_0003.nextval,
      3.1415926+sq_ustore_nullif_0003.nextval,
      i,
      lpad('abc','30','@'),
      3.1415926+sq_ustore_nullif_0003.nextval,
      i/4,
      i,
      to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ff'),
      lpad('abc','5000','a@123&^%djgk'),
      3.14+i,
      i+445.255,
      rpad('abc','30','&'),
      lpad('abc','30','&'),
      125563.141592,
      to_date('2019-03-1 14:58:54','yyyy-mm-dd hh24:mi:ss')
   );
    end loop;
end;
/

--step4:查询;expect:成功
select nullif(cast(1 as int),cast(1.00 as float)) ;
select nullif(cast(1 as int),cast(1.00 as decimal(7,2))) ;
select nullif(cast(1 as real),cast(1.00 as decimal(7,2))) ;
select nullif(cast(1 as number),cast(1.00 as decimal(7,2))) ;
select nullif(cast(1 as number),cast(1.00 as char(2))) ;
select nullif(cast(col_6 as char(20)),cast(col_14 as char(20))) from t_ustore_nullif_0003 order by col_6,col_14 limit 10;
select nullif(cast(col_6 as char(20)),cast(col_14 as varchar(10))) from t_ustore_nullif_0003 order by col_6,col_14 limit 10;
select nullif(cast(col_1 as char(10)),cast(col_7 as char(10))) tt from t_ustore_nullif_0003 order by col_1,col_7 limit 10;
select nullif(cast(col_1 as number),cast(col_7 as char(10))) tt from t_ustore_nullif_0003 order by col_1,col_7 limit 10;

--step5:清理环境;expect:成功
drop table if exists t_ustore_nullif_0003;
drop sequence if exists sq_ustore_nullif_0003;